A database is a collection of logically related data arranged in a predetermined format, such as in tables that contain rows and columns. To access the content of a table in the database, queries according to a standard database query language (such as the Structured Query Language or SQL) are submitted to the database. A query can be issued to insert new entries into a table of a database (such as to insert a row into the table), modify the content of the table, or to delete entries from the table. Examples of SQL statements include INSERT, SELECT, UPDATE, and DELETE.
Certain data analysis scenarios involve a computation of aggregates on a function of all column pair combinations. For example, a user may desire to compute the following aggregation: SUM(Ci*Cj) for all column combinations, for i=1 to K (K>1) and j=i to K. Thus, if there are 1000 columns (in other words, K=1000), then the possible range of values for is 1 to 1000, and the possible range of values for j is i to 1000. As a result, there would be 500,500 aggregates of the function SUM(Ci*Cj).
In one example, it is assumed that the base table is T1(C1, . . . , C1000) (in other words, the base table T1 has 1,000 columns C1 to C1000). A SQL query to compute the sums for all possible column combinations can be written as:
SELECT SUM(C1*C1), SUM(C1*C2), . . . , SUM(C1000*C1000) FROM T1
However, the foregoing SQL query cannot be realistically computed on a general purpose database system, since a typical database system may not have sufficient processing power to handle such a large query in a time-efficient manner. Moreover, the amount of memory that is available to perform the calculation of aggregates may not be sufficient for efficient computation of the aggregates, which will result in excessive swapping of data between the memory and relatively slow persistent storage (e.g., disk-based storage).